package com.midea.oms;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.midea.oms.Dto.QueryRes;
import com.midea.oms.Dto.Template;
import com.midea.oms.Dto.TemplateDetail;
import com.midea.oms.Dto.TemplateDetail2;
import com.midea.oms.Dto.Transport;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @projectName: 接口梳理
 * @package: com.midea.oms
 * @className: Test
 * @author: zhangcan30
 * @description: TODO
 * @date: 2023/12/29 18:01
 * @version: 1.0
 */
public class Test {


    public static void main(String[] args) throws FileNotFoundException {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //uat
//        dataSource.setUrl("jdbc:mysql://10.27.7.129:3306/logistics_otp_dispatch");
//        dataSource.setUsername("logistics_tms_w");
//        dataSource.setPassword("GK;9cJw6l#Ey");

        //生产
        dataSource.setUrl("jdbc:mysql://10.27.150.96:3306/logistics_otp_dispatch");
        dataSource.setUsername("logistics_tms_r");
        dataSource.setPassword("jqC9@DLlh8zP");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        //日期
        String startDate = "2024-04-16 00:00:00";
        String endDate = "2024-04-17 00:00:00";

        List<Template> templateList = new ArrayList<>();

        //临时标明细
        List<TemplateDetail> templateDetailList = new ArrayList<>();
//未使用算法明细
        List<TemplateDetail2> templateDetailList2 = new ArrayList<>();


        String businessCenter = null;
        String siteCode = null;

//        businessCenter = "成都配送";
//        siteCode = "'54394914343'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
//
//
//        businessCenter = "成都电商";
//        siteCode = "'54394913996'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————长沙————————————————————————————————————————————————————————————————

        businessCenter = "长沙北城配经营中心";
        siteCode = "'LMS0273'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————常德————————————————————————————————————————————————————————————————

        businessCenter = "常德经营中心";
        siteCode = "'54394914209','LMS0500'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————上海————————————————————————————————————————————————————————————————

        businessCenter = "上海城配经营中心";
        siteCode = "'LMS0049'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————武汉————————————————————————————————————————————————————————————————

        businessCenter = "武汉经营中心";
        siteCode = "'27574951964','54405334233'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————荆州————————————————————————————————————————————————————————————————

        businessCenter = "荆州经营中心";
        siteCode = "'3218','96540655813','LMS0214','LMS0361'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————南昌————————————————————————————————————————————————————————————————

        businessCenter = "南昌北经营中心";
        siteCode = "'54405334259'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————赣州————————————————————————————————————————————————————————————————

        businessCenter = "赣州经营中心";
        siteCode = "'53720928456','LMS0209'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————合肥————————————————————————————————————————————————————————————————

        businessCenter = "合肥城配经营中心";
        siteCode = "'54394914406'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————南宁————————————————————————————————————————————————————————————————

        businessCenter = "南宁经营中心";
        siteCode = "'LMS0048'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————昆明————————————————————————————————————————————————————————————————

        businessCenter = "昆明经营中心";
        siteCode = "'54394913982','54405334450'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————贵阳————————————————————————————————————————————————————————————————

        businessCenter = "贵阳经营中心";
        siteCode = "'54394914173','54405334278'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————广佛城配————————————————————————————————————————————————————————————————

        businessCenter = "广佛城配经营中心";
        siteCode = "'8356305895','7473764509','7070595096','LMS0154'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————广佛大件电商————————————————————————————————————————————————————————————————

        businessCenter = "广佛大件电商经营中心";
        siteCode = "'54394914138'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
//        //————————————————————————————————————————————————————————————海口经营中心————————————————————————————————————————————————————————————————
//
//        businessCenter = "海口经营中心";
//        siteCode = "'54394914031'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————茂名经营中心————————————————————————————————————————————————————————————————

        businessCenter = "茂名经营中心";
        siteCode = "'81090473938','LMS0206'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————东莞经营中心————————————————————————————————————————————————————————————————

        businessCenter = "东莞经营中心";
        siteCode = "'8355977384','LMS0203'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————深圳经营中心————————————————————————————————————————————————————————————————

        businessCenter = "深圳经营中心";
        siteCode = "'8355976338','LMS0198'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————福州经营中心————————————————————————————————————————————————————————————————

        businessCenter = "福州经营中心";
        siteCode = "'LMS0046','54394914056','LMS0280'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————厦门经营中心————————————————————————————————————————————————————————————————

        businessCenter = "厦门经营中心";
        siteCode = "'96540660777','LMS0196'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————宁波经营中心————————————————————————————————————————————————————————————————

        businessCenter = "宁波经营中心";
        siteCode = "'32836061305','LMS0199'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————温州经营中心————————————————————————————————————————————————————————————————

        businessCenter = "温州经营中心";
        siteCode = "'27574953029','LMS0230'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————泉州经营中心————————————————————————————————————————————————————————————————

        businessCenter = "泉州经营中心";
        siteCode = "'LMS0186'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————成都经营中心————————————————————————————————————————————————————————————————

        businessCenter = "成都经营中心";
        siteCode = "'54394914343','54394913996'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————眉山经营中心————————————————————————————————————————————————————————————————

        businessCenter = "眉山经营中心";
        siteCode = "'LMS0156'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————南充经营中心————————————————————————————————————————————————————————————————

        businessCenter = "南充经营中心";
        siteCode = "'54405334499','LMS0531'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————泸州城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "泸州城配经营中心";
        siteCode = "'96540660831','LMS0544'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————重庆城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "重庆城配经营中心";
        siteCode = "'LMS0047','54405334297'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————南京城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "南京城配经营中心";
        siteCode = "'54394914053','54394913991','LMS0164'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————淮安经营中心————————————————————————————————————————————————————————————————

        businessCenter = "淮安经营中心";
        siteCode = "'96540657696'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————徐州经营中心————————————————————————————————————————————————————————————————

        businessCenter = "徐州经营中心";
        siteCode = "'54394914149'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————苏南城配————————————————————————————————————————————————————————————————

        businessCenter = "苏南城配经营中心";
        siteCode = "'54394914048','LMS0200'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        //————————————————————————————————————————————————————————————西安城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "西安城配经营中心";
        siteCode = "'54405334414','54405334230'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————临沂经营中心————————————————————————————————————————————————————————————————

        businessCenter = "临沂经营中心";
        siteCode = "'96484551004','LMS0520'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————济南经营中心————————————————————————————————————————————————————————————————

        businessCenter = "济南经营中心";
        siteCode = "'32836061216','54405334294'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————潍坊经营中心————————————————————————————————————————————————————————————————

        businessCenter = "潍坊经营中心";
        siteCode = "'LMS0424','32836061142'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————青岛经营中心————————————————————————————————————————————————————————————————

        businessCenter = "青岛经营中心";
        siteCode = "'95074832750','LMS0201'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————济宁经营中心————————————————————————————————————————————————————————————————
//
//        businessCenter = "济宁经营中心";
//        siteCode = "'96484551774','LMS0519'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————菏泽经营中心————————————————————————————————————————————————————————————————

        businessCenter = "济宁经营中心";
        siteCode = "'LMS0169','LMS0508','96484551774','LMS0519'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————郑州城配经营中心/郑州经营中心————————————————————————————————————————————————————————————————

        businessCenter = "郑州经营中心";
        siteCode = "'27574949872','54394914267'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————新乡经营中心————————————————————————————————————————————————————————————————

        businessCenter = "新乡经营中心";
        siteCode = "'96484553686'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————驻马店经营中心————————————————————————————————————————————————————————————————

        businessCenter = "驻马店经营中心";
        siteCode = "'LMS0451','LMS0491'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————漯河经营中心————————————————————————————————————————————————————————————————

        businessCenter = "漯河经营中心";
        siteCode = "'75889459187'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————南阳经营中心————————————————————————————————————————————————————————————————

        businessCenter = "南阳经营中心";
        siteCode = "'96484554639','LMS0215'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————安阳经营中心————————————————————————————————————————————————————————————————

        businessCenter = "安阳经营中心";
        siteCode = "'LMS0406','LMS0476'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————洛阳经营中心————————————————————————————————————————————————————————————————

        businessCenter = "洛阳经营中心";
        siteCode = "'96484552441','LMS0220'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
        //————————————————————————————————————————————————————————————太原城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "太原城配经营中心";
        siteCode = "'54405334206','LMS0043'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
//        //————————————————————————————————————————————————————————————临汾经营中心————————————————————————————————————————————————————————————————
//
//        businessCenter = "临汾经营中心";
//        siteCode = "'96540659826','LMS0521'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————安庆经营中心————————————————————————————————————————————————————————————————

        businessCenter = "安庆经营中心";
        siteCode = "'95290700264','LMS0224'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————乌市城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "乌市城配经营中心";
        siteCode = "'96540660677','54394914349'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————乌市城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "乌市城配经营中心（剔除统配）";
        siteCode = "'54394914349'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————北京城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "北京城配经营中心";
        siteCode = "'LMS0045','54394913984'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————天津大件电商经营中心————————————————————————————————————————————————————————————————

        businessCenter = "天津大件电商经营中心";
        siteCode = "'54405334463','54405334262'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————邯郸经营中心————————————————————————————————————————————————————————————————

        businessCenter = "邯郸经营中心";
        siteCode = "'96540657853','LMS0487','96540657871'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————保定经营中心————————————————————————————————————————————————————————————————

        businessCenter = "保定经营中心";
        siteCode = "'LMS0432','4130311715','96540657897'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————石家庄城配经营中心————————————————————————————————————————————————————————————————

        businessCenter = "石家庄城配经营中心";
        siteCode = "'54405334426','54394913963'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————哈尔滨经营中心————————————————————————————————————————————————————————————————

        businessCenter = "哈尔滨经营中心";
        siteCode = "'LMS0050','54394914322'";
        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));

        //————————————————————————————————————————————————————————————沈阳经营中心————————————————————————————————————————————————————————————————
//
//        businessCenter = "沈阳经营中心";
//        siteCode = "'54394914185','54405334484'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));
//
//        //————————————————————————————————————————————————————————————长春经营中心————————————————————————————————————————————————————————————————
//
//        businessCenter = "长春经营中心";
//        siteCode = "'LMS0051','54405334549'";
//        templateList.addAll(getTemplates(businessCenter, startDate, endDate, siteCode, jdbcTemplate, templateDetailList, templateDetailList2));


        downloads(templateList, templateDetailList, templateDetailList2);

        System.out.println("——————————————————完成————————————————————————————————————————————");

    }

    private static void downloads(List<Template> templateList, List<TemplateDetail> templateDetailList, List<TemplateDetail2> templateDetailList2) throws FileNotFoundException {

//        String url="C:/Users/zhangcan30/Desktop/jar/导出.xlsx";
        String url = "E:/Mydata/Administrator/Desktop/java代码/导出.xlsx";

        FileOutputStream outputStream = new FileOutputStream(url);

        EasyExcel.write(outputStream, Template.class).sheet(0, "sheetName").doWrite(templateList);


        String ur2 = "E:/Mydata/Administrator/Desktop/java代码/临时标明细.xlsx";
        FileOutputStream outputStream2 = new FileOutputStream(ur2);

        EasyExcel.write(outputStream2, TemplateDetail.class).sheet(0, "sheetName").doWrite(templateDetailList);


        String ur3 = "E:/Mydata/Administrator/Desktop/java代码/未使用算法明细.xlsx";
        FileOutputStream outputStream3 = new FileOutputStream(ur3);

        EasyExcel.write(outputStream3, TemplateDetail2.class).sheet(0, "sheetName").doWrite(templateDetailList2);


    }

    private static List<Template> getTemplates(String businessCenter, String startDate, String endDate, String siteCode,
                                               JdbcTemplate jdbcTemplate, List<TemplateDetail> templateDetailList, List<TemplateDetail2> templateDetailList2) {

        System.out.println(businessCenter + "-----**********************--------开始");
        //直控线路
        String sql1 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',sum(t.total_volume) as volume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in (" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            " group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res1 = jdbcTemplate.query(sql1, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map1 = res1.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));

        //固定标
        String sql2 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume ) as volume \n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no\n" +
            "left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "left join receiver_customer_config ss on t.site_code =ss.site_code and t.receiver_name =ss.receiver_customer_name \n" +
            "and t.receiver_town_code=ss.receiver_town_code and t.receiver_mobile =ss.receiver_mobile and ss.active_flag =1\n" +
            "left join send_customer_config dd on t.site_code =dd.site_code and t.customer_code=dd.send_customer_code \n" +
            "and t.sender_district_code=dd.send_district_code and dd.active_flag =1\n" +
            "left join transport_appointment ta on ta.task_no =t.task_no\n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            "and ae.order_pool_flag !='ALG'\n" +
            "and JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag') is null\n" +
            "and ta.examine_time_update_flag !=1\n" +
            "group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res2 = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map2 = res2.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));

        System.out.println(businessCenter + "-----固定标查询得到" + res2);

        //使用过一键排车的
        String sql3 = "select date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume ) as volume\n" +
            "from transport_algorithm_extend tae \n" +
            "left join transport t on t.task_no =tae.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "left join algorithm_line_order o on tae.task_no =o.task_no \n" +
            " where t.create_time >'" + startDate + "'\n" +
            " and t.create_time <'" + endDate + "'\n" +
            " and t.site_code in(" + siteCode + ")\n" +
            " and t.delete_flag =0\n" +
            " and tae.order_pool_flag ='ALG'\n" +
            "and t.freight_basis !='40'\n" +
            "and t.delivery_type not in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "and ta.examine_time_update_flag !=1\n" +
            " and o.delete_flag =0\n" +
            " and o.scheme_type ='OP'\n" +
            " and o.cancel_flag !=1\n" +
            " and o.version !=0\n" +
            " group by date_format(t.create_time,'%Y-%m-%d')\n" +
            "order by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res3 = jdbcTemplate.query(sql3, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map3 = res3.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));

        //实际使用算法排车的订单数量
        String sql4 = "select date_format(t.create_time,'%Y-%m-%d') as 'date' ,\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',sum(t.total_volume ) as volume\n" +
            "from transport_algorithm_extend tae \n" +
            "left join transport t on t.task_no =tae.task_no \n" +
            "left join algorithm_line_order o on tae.task_no =o.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "left join algorithm_line_detail d on o.train_num =d.train_num \n" +
            " where t.create_time >'" + startDate + "'\n" +
            " and t.create_time <'" + endDate + "'\n" +
            " and t.site_code in(" + siteCode + ")\n" +
            " and t.delete_flag =0\n" +
            "and t.freight_basis !='40'\n" +
            "and t.delivery_type not in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "and ta.examine_time_update_flag !=1\n" +
            " and tae.order_pool_flag ='ALG'\n" +
            " and o.delete_flag =0\n" +
            " and o.scheme_type ='OP'\n" +
            " and o.cancel_flag =0\n" +
            " and o.version !=0\n" +
            " and d.delete_flag =0\n" +
            " and d.scheme_type ='OP'\n" +
            " and d.cancel_flag ='5'\n" +
            " group by date_format(t.create_time,'%Y-%m-%d')\n" +
            "order by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res4 = jdbcTemplate.query(sql4, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map4 = res4.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));

        //调整-》转移
        String sql5 = "select date_format(op.create_time,'%Y-%m-%d') as 'date' ,\n" +
            "count(date_format(op.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(op.total_volume) as volume\n" +
            "from (\n" +
            "\n" +
            "select t.create_time,t.receiver_city_name,t.total_volume ,o.delete_flag ,o.line_code ,o.train_num ,o.task_no ,o.cancel_flag ,o.scheme_type,d.delete_flag as df \n" +
            "from transport_algorithm_extend tae \n" +
            "left join transport t on t.task_no =tae.task_no \n" +
            "left join algorithm_line_order o on o.task_no =t.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "left join algorithm_line_detail d on o.train_num =d.train_num \n" +
            " where t.create_time >'" + startDate + "'\n" +
            " and t.create_time <'" + endDate + "'\n" +
            " and t.site_code in(" + siteCode + ")\n" +
            " and t.delete_flag =0\n" +
            "and t.freight_basis !='40'\n" +
            "and t.delivery_type not in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "and ta.examine_time_update_flag !=1\n" +
            " and tae.order_pool_flag ='ALG'\n" +
            " and o.scheme_type ='OP'\n" +
            " and o.cancel_flag =0\n" +
            "and d.scheme_type ='OP'\n" +
            "and d.cancel_flag =5\n" +
            "group by o.task_no\n" +
            " ) op\n" +
            "left join (\n" +
            "select t.create_time,t.receiver_city_name ,o.delete_flag ,o.line_code ,o.train_num ,o.task_no ,o.cancel_flag ,o.scheme_type,d.delete_flag as df\n" +
            "from transport_algorithm_extend tae \n" +
            "left join transport t on t.task_no =tae.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "left join algorithm_line_order o on o.task_no =t.task_no \n" +
            "left join algorithm_line_detail d on o.train_num =d.train_num \n" +
            " where t.create_time >'" + startDate + "'\n" +
            "  and t.create_time <'" + endDate + "'\n" +
            " and t.site_code in(" + siteCode + ")\n" +
            " and t.delete_flag =0\n" +
            "and t.freight_basis !='40'\n" +
            "and t.delivery_type not in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "and ta.examine_time_update_flag !=1\n" +
            " and tae.order_pool_flag ='ALG'\n" +
            " and o.scheme_type ='OPS'\n" +
            " and o.cancel_flag =0\n" +
            "and d.scheme_type ='OP'\n" +
            "and d.cancel_flag =5 ) ops on op.task_no=ops.task_no and op.line_code=ops.line_code\n" +
            "where\n" +
            "ops.line_code  is not null\n" +
            "and \n" +
            "(op.delete_flag=0 and op.train_num!=ops.train_num)\n" +
            "group by date_format(op.create_time,'%Y-%m-%d')\n" +
            "order by date_format(op.create_time,'%Y-%m-%d')";
        List<QueryRes> res5 = jdbcTemplate.query(sql5, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map5 = res5.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));


        //临时标的
        String sql6 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume) as volume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no\n" +
            "left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            "and ae.order_pool_flag !='ALG'\n" +
            "and JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag') is not null\n" +
            "and JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')!='2'\n" +
            "group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res6 = jdbcTemplate.query(sql6, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map6 = res6.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));

        //搬仓的
        String sql7 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume) as volume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no\n" +
            "left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            "and t.freight_basis ='40'\n" +
            "group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res7 = jdbcTemplate.query(sql7, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map7 = res7.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));


        //直配+网点配送
        String sql8 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume) as volume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no\n" +
            "left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            "and t.delivery_type in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res8 = jdbcTemplate.query(sql8, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map8 = res8.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));


        //改约
        String sql9 = "select \n" +
            "date_format(t.create_time,'%Y-%m-%d') as 'date',\n" +
            "count(date_format(t.create_time,'%Y-%m-%d')) as 'taskNum',\n" +
            "sum(t.total_volume) as volume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no\n" +
            "left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1\n" +
            "and ta.examine_time_update_flag =1\n" +
            "group by date_format(t.create_time,'%Y-%m-%d')";
        List<QueryRes> res9 = jdbcTemplate.query(sql9, new BeanPropertyRowMapper<>(QueryRes.class));
        Map<String, QueryRes> map9 = res9.stream().collect(Collectors.toMap(QueryRes::getDate, Function.identity(), (oldVal, newVal) -> newVal));


        System.out.println(businessCenter + "-----临时标查询得到" + res6);

        List<Template> templates = new ArrayList<>();

        for (Map.Entry<String, QueryRes> entry : map1.entrySet()) {
            String date = entry.getKey();
            QueryRes directDTO = entry.getValue();

            if (ObjectUtils.isEmpty(directDTO)) {
                continue;
            }

            Template res = new Template();
            res.setDate(date);
            res.setBusinessCenter(businessCenter);
            res.setDirectLineTaskNum(directDTO.getTaskNum());
            res.setDirectLineVolume(directDTO.getVolume());

            QueryRes dto2 = map2.get(date);
            if (!ObjectUtils.isEmpty(dto2)) {
                res.setFixedLabelTaskNum(dto2.getTaskNum());
                res.setFixedLabelVolume(dto2.getVolume());
            }

            QueryRes dto6 = map6.get(date);
            if (!ObjectUtils.isEmpty(dto6)) {
                res.setTemLabelTaskNum(dto6.getTaskNum());
                res.setTemLabelVolume(dto6.getVolume());
            }

            QueryRes dto7 = map7.get(date);
            if (!ObjectUtils.isEmpty(dto7)) {
                res.setTemLabelMoveTaskNum(dto7.getTaskNum());
                res.setTemLabelMoveVolume(dto7.getVolume());
            }

            QueryRes dto8 = map8.get(date);
            if (!ObjectUtils.isEmpty(dto8)) {
                res.setTemLabelDeliveryTaskNum(dto8.getTaskNum());
                res.setTemLabelDeliveryVolume(dto8.getVolume());
            }

            QueryRes dto9 = map9.get(date);
            if (!ObjectUtils.isEmpty(dto9)) {
                res.setChangeAppointmentTaskNum(dto9.getTaskNum());
                res.setChangeAppointmentVolume(dto9.getVolume());
            }

            res.setShouldLabelTaskNum(res.getDirectLineTaskNum() - res.getFixedLabelTaskNum() - res.getTemLabelMoveTaskNum() - res.getTemLabelDeliveryTaskNum() - res.getChangeAppointmentTaskNum());
            res.setShouldLabelVolume(calSubstract(res.getDirectLineVolume(), res.getFixedLabelVolume(), res.getTemLabelMoveVolume(), res.getTemLabelDeliveryVolume(), res.getChangeAppointmentVolume()));


            QueryRes dto3 = map3.get(date);
            if (!ObjectUtils.isEmpty(dto3)) {
                res.setOnClickTaskNum(dto3.getTaskNum());
                res.setOnClickVolume(dto3.getVolume());
            }

            QueryRes dto4 = map4.get(date);
            if (!ObjectUtils.isEmpty(dto4)) {
                res.setActualTaskNum(dto4.getTaskNum());
                res.setActualVolume(dto4.getVolume());
            }

            QueryRes dto5 = map5.get(date);
            if (ObjectUtils.isEmpty(dto5)) {
                dto5 = new QueryRes();
                dto5.setVolume(BigDecimal.ZERO);
                dto5.setTaskNum(0);
            }
            res.setDirectTaskNum(res.getActualTaskNum() - dto5.getTaskNum());
            res.setDirectVolume(calSubstract(res.getActualVolume(), dto5.getVolume()));

            res.setUseTaskNumRate(calDivide(res.getOnClickTaskNum(), res.getShouldLabelTaskNum()));
            res.setUseVolumeRate(calDivide(res.getOnClickVolume(), res.getShouldLabelVolume()));


            res.setAdoptTaskNumRate(calDivide(res.getDirectTaskNum(), res.getActualTaskNum()));
            System.out.println(MessageFormat.format("{0},直接采纳订单数{1},真实排车{2},计算得到{3}", businessCenter, res.getDirectTaskNum(), res.getActualTaskNum(), res.getAdoptTaskNumRate()));
            res.setAdoptVolumeRate(calDivide(res.getDirectVolume(), res.getActualVolume()));
            templates.add(res);
        }


        //临时标明细
        String sqlTemDetail = "select \n" +
            "            date_format(t.create_time,'%Y-%m-%d') as 'date', \n" +
            "            t.task_no as taskNo,\n" +
            "            t.customer_order_no as customerOrderNo ,\n" +
            "            case \n" +
            "            when JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')='1' then '不可集拼类项目客户' \n" +
            "            when JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')='2' then '同仓/搬仓' \n" +
            "            when JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')='3' then '限定非直控运力特殊场景' \n" +
            "            when JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')='4' then '不进算法，进入手工LSP的发货客户' \n" +
            "            when JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')='5' then '纯运输' \n" +
            "            end as temMark,\n" +
            "            t.customer_name as customerName,\n" +
            "            t.total_volume as totalVolume\n" +
            "            from transport t \n" +
            "            left join transport_extend te on t.task_no =te.task_no\n" +
            "            left join transport_algorithm_extend ae on ae.task_no =t.task_no \n" +
            "            left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "            where t.delete_flag =0\n" +
            "and t.site_code in(" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "            and te.direct_control_flag =1\n" +
            "            and ae.order_pool_flag !='ALG'\n" +
            "            and JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag') is not null\n" +
            "            and JSON_EXTRACT(ta.sign_desc ,'$.notUseDirectFlag')!='2'\n" +
            "            group by date_format(t.create_time,'%Y-%m-%d'),t.task_no ";
        List<TemplateDetail> sqlTemDetailRes = jdbcTemplate.query(sqlTemDetail, new BeanPropertyRowMapper<>(TemplateDetail.class));
        if (!CollectionUtils.isEmpty(sqlTemDetailRes)) {
            sqlTemDetailRes.forEach(o -> o.setBusinessCenter(businessCenter));
            templateDetailList.addAll(sqlTemDetailRes);
        }

        //直控线路明细
        String sqlDirectDetail = "select \n" +
            "            date_format(t.create_time,'%Y-%m-%d') as 'date', \n" +
            "            t.task_no as taskNo,\n" +
            "            t.customer_order_no as customerOrderNo ,\n" +
            "            t.customer_name as customerName,\n" +
            "            t.sender_city_name as senderCityName,\n" +
            "            t.sender_district_name as senderDistrictName,\n" +
            "            t.sender_town_name as senderTownName,\n" +
            "            t.receiver_city_name as receiverCityName,\n" +
            "            t.receiver_district_name as receiverDistrictName,\n" +
            "            t.receiver_town_name as receiverTownName,\n" +
            "            t.total_volume as totalVolume\n" +
            "from transport t \n" +
            "left join transport_extend te on t.task_no =te.task_no \n" +
            "where t.delete_flag =0\n" +
            "and t.site_code in (" + siteCode + ")\n" +
            "and t.create_time >'" + startDate + "'\n" +
            "and t.create_time <'" + endDate + "'\n" +
            "and te.direct_control_flag =1";
        List<TemplateDetail2> resultDirectLine = jdbcTemplate.query(sqlDirectDetail, new BeanPropertyRowMapper<>(TemplateDetail2.class));


        //使用算法明细
        String sqlUseAlg = "select t.task_no as taskNo\n" +
            "from transport_algorithm_extend tae \n" +
            "left join transport t on t.task_no =tae.task_no \n" +
            "left join transport_appointment ta on ta.task_no =t.task_no \n" +
            "left join algorithm_line_order o on tae.task_no =o.task_no \n" +
            " where t.create_time >'" + startDate + "'\n" +
            " and t.create_time <'" + endDate + "'\n" +
            " and t.site_code in(" + siteCode + ")\n" +
            " and t.delete_flag =0\n" +
            "and t.freight_basis !='40'\n" +
            "and t.delivery_type not in('WAREHOUSEMATCHING','NET_MATCHING')\n" +
            "and ta.examine_time_update_flag !=1\n" +
            " and tae.order_pool_flag ='ALG'\n" +
            " and o.delete_flag =0\n" +
            " and o.scheme_type ='OP'\n" +
            " and o.cancel_flag !=1\n" +
            " and o.version !=0;";
        List<TemplateDetail2> resultUseAlg = jdbcTemplate.query(sqlUseAlg, new BeanPropertyRowMapper<>(TemplateDetail2.class));

        List<String> taskNos = resultUseAlg.stream().map(TemplateDetail2::getTaskNo).collect(Collectors.toList());


        List<TemplateDetail2> result4 = new ArrayList<>();

        if (!CollectionUtils.isEmpty(resultDirectLine)) {
            for (int i = 0; i < resultDirectLine.size(); i++) {
                resultDirectLine.get(i).setBusinessCenter(businessCenter);
                if (!taskNos.contains(resultDirectLine.get(i).getTaskNo())) {
                    result4.add(resultDirectLine.get(i));
                }
            }
            templateDetailList2.addAll(result4);
        }

        return templates;
    }

    public static BigDecimal calSubstract(BigDecimal val1, BigDecimal val2) {
        if (ObjectUtils.isEmpty(val1)) {
            val1 = BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val2)) {
            val2 = BigDecimal.ZERO;
        }
        return val1.subtract(val2);
    }

    public static BigDecimal calSubstract(BigDecimal val1, BigDecimal val2, BigDecimal val3, BigDecimal val4, BigDecimal val5) {
        if (ObjectUtils.isEmpty(val1)) {
            val1 = BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val2)) {
            val2 = BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val3)) {
            val3 = BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val4)) {
            val4 = BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val5)) {
            val5 = BigDecimal.ZERO;
        }

        return val1.subtract(val2).subtract(val3).subtract(val4).subtract(val5);
    }

    public static BigDecimal calDivide(BigDecimal val1, BigDecimal val2) {
        if (ObjectUtils.isEmpty(val1) || BigDecimal.ZERO.compareTo(val1) == 0) {
            return BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val2) || BigDecimal.ZERO.compareTo(val2) == 0) {
            return BigDecimal.ZERO;
        }
        return val1.divide(val2, 4, BigDecimal.ROUND_HALF_UP);
    }

    public static BigDecimal calDivide(Integer val1, Integer val2) {
        if (ObjectUtils.isEmpty(val1) || val1 == 0) {
            return BigDecimal.ZERO;
        }
        if (ObjectUtils.isEmpty(val2) || val2 == 0) {
            return BigDecimal.ZERO;
        }
        BigDecimal a = BigDecimal.valueOf(val1);
        BigDecimal b = BigDecimal.valueOf(val2);
        return a.divide(b, 4, BigDecimal.ROUND_HALF_UP);
    }

}
